package cre.tool.core;

import cre.tool.domain.Album;
import cre.tool.util.DbUtils;
import org.apache.poi.xssf.usermodel.XSSFCell;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.*;

/**
 * @Author penglai
 * @Date 2017/2/24.
 */
public class DbHandler {

    private static final String CATEGORY_ALBUM_INSERT = "INSERT INTO `duotin_category_album` (`album_id`, `category_id`, `sub_category_id`, `is_del`) " +
            "VALUES ";
    private static final String SUB_CATEGORY = "INSERT INTO `duotin_category` (`parent_id`, `title`, `info`, `type`) VALUES";

    public boolean isSingle(String categoryId) throws SQLException {
        Connection connection = gainConnection();
        String sql = "select type from duotin_category where id=" + categoryId;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        int type = 0;
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            type = resultSet.getInt(1);
        }

        preparedStatement.close();
        connection.close();
        return type == 2;
    }

    public boolean isParent(String categoryId) throws SQLException {
        Connection connection = gainConnection();
        String sql = "select parent_id from duotin_category where id=" + categoryId;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        int type = -1;
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            type = resultSet.getInt(1);
        }

        preparedStatement.close();
        connection.close();
        return type == 0;
    }

    public void insertCategoryAlbums(Map<Integer, List<Integer>> categoryAlbums, String categoryId) throws SQLException {
        StringBuffer sb = null;
        boolean exist = false;

        for (Integer key : categoryAlbums.keySet()) {
            List<Integer> albums = categoryAlbums.get(key);
            List<Integer> albums2 = selectBySubCategoryId(categoryId, key);

            sb = new StringBuffer();
            for (int i = 0; i < albums.size(); i++) {
                Integer aid = albums.get(i);
                if (albums2.contains(aid)) {
                    continue;
                }
                exist = true;
                String s = "(" + albums.get(i) + "," + categoryId + "," + key + ",0)";
                sb.append(s);

                if (i < albums.size() - 1) {
                    sb.append(",");
                }
            }
            if (exist)
                doInsert(CATEGORY_ALBUM_INSERT + sb.toString());
        }
    }

    public void insertSubCategory(String categoryId, List<String> subCategory) throws SQLException {
        StringBuffer sb = new StringBuffer();
        ;

        for (int i = 0; i < subCategory.size(); i++) {
            String sub = subCategory.get(i);
            String s = "(" + categoryId + ",'" + sub + "','" + sub + "',2)";
            sb.append(s);

            if (i < subCategory.size() - 1) {
                sb.append(",");
            }
        }
        doInsert(SUB_CATEGORY + sb.toString());
    }


    private void doInsert(String sql) throws SQLException {
        Connection connection = gainConnection();
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        preparedStatement.executeUpdate();

        preparedStatement.close();
        connection.close();
    }

    private List<Integer> selectBySubCategoryId(String categoryId, Integer key) throws SQLException {
        Connection connection = gainConnection();
        String sql = "select album_id from duotin_category_album where category_id=" + categoryId + "" +
                " and sub_category_id= " + key + " and is_del=0";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        List<Integer> ret = new ArrayList<>();
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            ret.add(resultSet.getInt(1));
        }

        preparedStatement.close();
        connection.close();
        return ret;
    }

    protected static Connection gainConnection() {
        try {
            return DbUtils.gainConnection(Configuration.getUrl(), Configuration.getUser(), Configuration.getPassword());
        } catch (ClassNotFoundException e) {
            e.printStackTrace();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return null;
    }

    public Map<Integer, String> selectSubMaps(String parentId) throws SQLException {
        Connection connection = gainConnection();
        String sql = "select id, title from duotin_category where is_del=0 and parent_id=" + parentId;
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        Map<Integer, String> ret = new HashMap<>();
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            ret.put(resultSet.getInt(1), resultSet.getString(2));
        }

        preparedStatement.close();
        connection.close();
        return ret;
    }

    public List<Album> selectByCategoryId(String parentId) throws SQLException {
        Connection connection = gainConnection();
        String sql = "SELECT  a.id,a.title,a.info from duotin_album a LEFT JOIN duotin_category_album c ON c.album_id=a.id where 1=1  and a.is_del=0 and status=1 and c.is_del=0 and c.category_id=" + parentId + " order by a.id desc";
        PreparedStatement preparedStatement = connection.prepareStatement(sql);

        List<Album> ret = new ArrayList<>();
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            Album album = new Album(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
            ret.add(album);
        }

        preparedStatement.close();
        connection.close();
        return ret;
    }

    public List<Album> selectAlbumByIn(List<String> albums, Integer type) throws Exception {
        Connection connection = gainConnection();
        String sql = "select id,title,info,status,is_del,collect_count,real_collect_count,play_count,real_play_count,download_count," +
                "real_download_count,like_count,real_like_count from duotin_album ";
        String where = gainIns(albums, type);

        PreparedStatement preparedStatement = connection.prepareStatement(sql + where);

        List<Album> ret = new ArrayList<>();
        ResultSet resultSet = preparedStatement.executeQuery();

        while (resultSet.next()) {
            Album album = new Album(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
            album.status(resultSet.getInt(4)).isDel(resultSet.getInt(5))
                    .collectCount(resultSet.getInt(6)).realCollectCount(resultSet.getInt(7))
                    .playCount(resultSet.getInt(8)).realPlayCount(resultSet.getInt(9))
                    .downloadCount(resultSet.getInt(10)).realDownloadCount(resultSet.getInt(11))
                    .likeCount(resultSet.getInt(12)).realLikeCount(resultSet.getInt(13));

            ret.add(album);
        }

        preparedStatement.close();
        connection.close();
        return ret;
    }

    private String gainIns(List<String> albums, Integer type) {
        if (XSSFCell.CELL_TYPE_NUMERIC == type) {
            StringBuffer sb = new StringBuffer(" where id in ")
                    .append("(");

            StringBuffer in = new StringBuffer();
            for (int i = 0; i < albums.size(); i++) {
                String a = albums.get(i);
                in.append(a);

                if (i < albums.size() - 1) {
                    in.append(",");
                }
            }
            sb.append(in.toString()).append(")").append(" order by field(id,").append(in.toString()).append(")");
            return sb.toString();
        } else {

            StringBuffer sb = new StringBuffer(" where title in ")
                    .append("(");

            StringBuffer in = new StringBuffer();
            for (int i = 0; i < albums.size(); i++) {
                String a = albums.get(i);
                in.append("'").append(a).append("'");

                if (i < albums.size() - 1) {
                    in.append(",");
                }
            }
            sb.append(in.toString()).append(")").append(" order by field(title,").append(in.toString()).append(")");
            return sb.toString();
        }
    }
}
